import pymysql
from decimal import Decimal

# db = pymysql.connect(host='10.132.141.99', port=3306, user='root', passwd='1qaz2wsx', db='tpch_1G', charset='utf8')
db = pymysql.connect(host='192.168.137.2', port=3306, user='root', passwd='1qaz2wsx', db='tpch_1G', charset='utf8')
log_level = 1


def init(default_db, log=1, default_charset='utf8'):
    global db
    global log_level
    db = pymysql.connect(host='192.168.137.2', port=3306, user='root', passwd='1qaz2wsx', db=default_db, charset=default_charset)
    log_level = log


def save(table, obj):
    cols = '('
    for key in obj.keys():
        cols = cols + '`' + key + '`' + ','
    cols = cols[:-1] + ')'
    values = '('
    for key in obj.keys():
        value = obj[key]
        if isinstance(value, str):
            values = values + "'" + value.replace('\'', '\'\'') + "'" + ','
        else:
            values = values + str(value) + ','
    values = values[:-1] + ')'
    sql = 'INSERT INTO ' + table + cols + ' VALUES' + values
    if log_level > 0:
        print(sql)
    cursor = db.cursor()
    cursor.execute(sql)
    db.commit()


def insert(table, objs):
    if len(objs) < 1:
        return
    cols = '('
    for key in objs[0].keys():
        cols = cols + '`' + key + '`' + ','
    cols = cols[:-1] + ')'
    values = ''
    for obj in objs:
        values = values + '('
        for key in obj.keys():
            value = obj[key]
            if isinstance(value, str):
                values = values + "'" + value.replace('\'', '\'\'') + "'" + ','
            else:
                values = values + str(value) + ','
        values = values[:-1] + '),'
    values = values[:-1]
    sql = 'INSERT INTO ' + table + cols + ' VALUES' + values
    if log_level > 0:
        print(sql)
    cursor = db.cursor()
    cursor.execute(sql)
    db.commit()


def update(table, obj, where):
    values = ' SET '
    for key in obj.keys():
        value = obj[key]
        if isinstance(value, str):
            values = values + key + "='" + value + "'" + ','
        else:
            values = values + key + "=" + str(value) + ','
    values = values[:-1]
    sql = 'UPDATE ' + table + values + ' WHERE ' + where
    if log_level > 0:
        print(sql)
    cursor = db.cursor()
    cursor.execute(sql)
    db.commit()


def query(sql):
    if log_level > 0:
        print(sql)
    cursor = db.cursor()
    cursor.execute(sql)
    index = cursor.description
    result = []
    for res in cursor.fetchall():
        row = {}
        for i in range(len(index)):
            if isinstance(res[i], Decimal):
                row[index[i][0]] = float(res[i])
            else:
                row[index[i][0]] = res[i]
        result.append(row)
    return result


def execute(sql):
    if log_level > 0:
        print(sql)
    cursor = db.cursor()
    cursor.execute(sql)
    db.commit()
